package fu.capstone.hanoicityguide.dataaccess.food;

import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import fu.capstone.hanoicityguide.R;
import fu.capstone.hanoicityguide.business.model.Food;
import fu.capstone.hanoicityguide.business.model.GeoCoordinate;
import fu.capstone.hanoicityguide.dataaccess.base.BaseDAOImpl;
import fu.capstone.hanoicityguide.dataaccess.base.DatabaseHelper;

public class FoodDAOImp extends BaseDAOImpl implements FoodDAO {

	private SQLiteDatabase database;
	private DatabaseHelper dbHelper;
	private Context context;

	public FoodDAOImp(Context ctx) {
		super(ctx);
		this.context = ctx;
	}

	@Override
	public ArrayList<Food> listFood(String sorter) {
		// TODO Auto-generated method stub
		Cursor cursor = null;
		ArrayList<Food> arrResult = new ArrayList<Food>();
		try {
			dbHelper = connectDB();
			database = dbHelper.getReadableDatabase();
			String queryFood = "SELECT Food.[Id], Food.[Name], Food.[Description], Rating.[RatingValue], "
					+ "AlbumImage.[IconPath] FROM Food "
					+ "LEFT JOIN Rating ON Food.[RatingID] = Rating.[Id]"
					+ "LEFT JOIN AlbumImage ON Food.[AlbumImageID] = AlbumImage.[Id]";
			// sort
			if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[0])) {
				queryFood = queryFood + "ORDER BY Rating.[RatingValue] DESC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[1])) {
				queryFood = queryFood + "ORDER BY Rating.[RatingValue] ASC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[2])) {
				queryFood = queryFood + "ORDER BY Food.[Name] DESC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[3])) {
				queryFood = queryFood + "ORDER BY Food.[Name] ASC";
			}
			cursor = database.rawQuery(queryFood, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					do {
						Food food = new Food();
						food.setId(cursor.getInt(cursor.getColumnIndex("Id")));
						food.setName(cursor.getString(cursor
								.getColumnIndex("Name")));
						food.setRating(cursor.getInt(cursor
								.getColumnIndex("RatingValue")));
						food.setIconImage(cursor.getString(cursor
								.getColumnIndex("IconPath")));
						arrResult.add(food);
					} while (cursor.moveToNext());
				}
			}
		} catch (Exception e) {
			Log.v("Can't query DB", e.toString());
		} finally {
			closeDB(database, dbHelper);
		}
		return arrResult;

	}

	@Override
	public Food browseFood(int foodID) {
		// TODO Auto-generated method stub
		Cursor cursor = null;
		Food food = new Food();
		ArrayList<String> arrImage = new ArrayList<String>();

		try {
			dbHelper = connectDB();
			database = dbHelper.getReadableDatabase();
			String queryFood = "SELECT Food.[Id], Food.[Name], Food.[Description], "
					+ "AlbumImage.[ImagePath1], AlbumImage.[ImagePath2], AlbumImage.[ImagePath3], "
					+ "AlbumImage.[ImagePath4], AlbumImage.[ImagePath5] , Geolocation.[Longitude] , Geolocation.[Latitude] , Food.[Price] , Food.[Address] , Food.[PhoneNumber] FROM Food "
					+ "LEFT JOIN AlbumImage ON Food.[AlbumImageId] = AlbumImage.[Id] "
					+ "LEFT JOIN Geolocation ON Food.[GeoId] = Geolocation.[Id] "
					+ "WHERE Food.[Id] == " + foodID;

			cursor = database.rawQuery(queryFood, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					do {
						food.setId(cursor.getInt(cursor.getColumnIndex("Id")));
						food.setName(cursor.getString(cursor
								.getColumnIndex("Name")));
						food.setDesc(cursor.getString(cursor
								.getColumnIndex("Description")));
						food.setGeo(new GeoCoordinate(
								Double.parseDouble(cursor.getString(cursor
										.getColumnIndex("Longitude"))), Double
										.parseDouble(cursor.getString(cursor
												.getColumnIndex("Latitude")))));
						food.setPrice(cursor.getFloat(cursor
								.getColumnIndex("Price")));
						food.setAddress(cursor.getString(cursor
								.getColumnIndex("Address")));
						food.setPhoneNumber(cursor.getString(cursor
								.getColumnIndex("PhoneNumber")));

						arrImage.add(cursor.getString(cursor
								.getColumnIndex("ImagePath1")));
						arrImage.add(cursor.getString(cursor
								.getColumnIndex("ImagePath2")));
						arrImage.add(cursor.getString(cursor
								.getColumnIndex("ImagePath3")));
						arrImage.add(cursor.getString(cursor
								.getColumnIndex("ImagePath4")));
						arrImage.add(cursor.getString(cursor
								.getColumnIndex("ImagePath5")));
						food.setArrImage(arrImage);

					} while (cursor.moveToNext());
				}
			}
		} catch (Exception e) {
			Log.v("Can't query DB", e.toString());
		} finally {
			closeDB(database, dbHelper);
		}
		return food;
	}

	@Override
	public ArrayList<Food> searchFood(String keyword, String sorter) {
		// TODO Auto-generated method stub
		Cursor cursor = null;
		ArrayList<Food> arrResult = new ArrayList<Food>();
		try {
			dbHelper = connectDB();
			database = dbHelper.getReadableDatabase();
			String queryFood = "SELECT Food.[Id], Food.[Name], Food.[Description], Rating.[RatingValue], "
					+ "AlbumImage.[IconPath] FROM Food "
					+ "LEFT JOIN Rating ON Food.[RatingID] = Rating.[Id]"
					+ "LEFT JOIN AlbumImage ON Food.[AlbumImageID] = AlbumImage.[Id] ";

			// filter by keyword
			queryFood = queryFood + "WHERE Food.[Name] LIKE '%" + keyword
					+ "%' ";
			// sort
			if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[0])) {
				queryFood = queryFood + "ORDER BY Rating.[RatingValue] DESC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[1])) {
				queryFood = queryFood + "ORDER BY Rating.[RatingValue] ASC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[2])) {
				queryFood = queryFood + "ORDER BY Food.[Name] DESC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[3])) {
				queryFood = queryFood + "ORDER BY Food.[Name] ASC";
			}

			cursor = database.rawQuery(queryFood, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					do {
						Food food = new Food();
						food.setId(cursor.getInt(cursor.getColumnIndex("Id")));
						food.setName(cursor.getString(cursor
								.getColumnIndex("Name")));
						food.setRating(cursor.getInt(cursor
								.getColumnIndex("RatingValue")));
						food.setIconImage(cursor.getString(cursor
								.getColumnIndex("IconPath")));
						arrResult.add(food);
					} while (cursor.moveToNext());
				}
			}
		} catch (Exception e) {
			Log.v("Can't query DB", e.toString());
		} finally {
			closeDB(database, dbHelper);
		}
		return arrResult;
	}

}
